const connection = require('../app/database');

class serviceMoment {

  async createMoment(userId, content) {
    const [result] = await connection.execute('INSERT INTO moment (user_id, content) VALUES (?, ?)', [userId, content]);
    return result;
  }

  async getMomentDetails(momentId) {
try {
        const sql = `SELECT 
      m.content ,m.id ,m.createAt ,m.updateAt,
      JSON_OBJECT('id',u.id,'name',u.name) user,
      (SELECT COUNT(*) FROM comment c WHERE c.moment_id =m.id ) commentCount,
      (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id =m.id ) labelCount,
      JSON_ARRAYAGG(JSON_OBJECT('id',l.id,'name',l.name) )labels,
      (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:3000/moment/images/',f.filename)) 
      FROM file f WHERE f.moment_id = m.id ) images
      FROM moment m 
      LEFT JOIN users u ON m.user_id = u.id
      LEFT JOIN moment_label ml ON ml.moment_id = m.id
      LEFT JOIN label l on ml.label_id = l.id 
      WHERE m.id = ?;
                 `;
  const [result] = await connection.execute(sql, [momentId]);
  return result[0];
}catch (e) {
  console.log(e)
}
  }

  async getMomentList(offset, size) {
    const sql = `SELECT 
          m.content ,m.id ,m.createAt ,m.updateAt,
          JSON_OBJECT('id',u.id,'name',u.name) user,
          (SELECT COUNT(*) FROM comment c WHERE c.moment_id =m.id ) commentCount,
          (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id =m.id ) labelCount,
          JSON_ARRAYAGG(JSON_OBJECT('id',l.id,'name',l.name) )labels,
          (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:3000/moment/images/',f.filename)) 
          FROM file f WHERE f.moment_id = m.id ) images
          FROM moment m 
          LEFT JOIN users u ON m.user_id = u.id
          LEFT JOIN moment_label ml ON ml.moment_id = m.id
          LEFT JOIN label l on ml.label_id = l.id 
          LIMIT ?,?;
                 `;
    const [result] = await connection.execute(sql, [offset, size]);
    return result;
  }
  async updateMoment(momentId, content) {
    const [result] = await connection.execute('UPDATE moment SET content = ? WHERE id = ?', [content, momentId]);
    return result;
  }
  async deleteMoment(momentId) {
    const [result] = await connection.execute('DELETE FROM moment WHERE id = ?', [momentId]);
    return result;
  }
  async getFileInfoByfilename(filename) {
    const [result] = await connection.execute('SELECT * FROM file WHERE filename = ?', [filename]);
    return result[0];
  }

  }


module.exports = new serviceMoment();